package com.wdzl.dao;

import com.wdzl.entity.ConditionBean;
import com.wdzl.entity.Employee;

import java.util.ArrayList;
import java.util.List;

public class EmpDao extends BaseDao {
    private int countPerPage = 15; //每页显示记录数

    //查询所有的员工
    public List<Employee> getEmps(int page, ConditionBean condition) {
        List<Employee> list = new ArrayList<>();
        String sql = "select e.id,e.name,d.name deptName,salary,sex,email,pNumber from departinfo d,empInfo e where d.id=e.did ";
//		String sql="select * from (select e.*,rownum rn from empinfo e where rownum<="+(page*15);
        if (condition != null) {
            if (condition.getDid() != 0) {
                sql += " and did='" + condition.getDid() + "'";
            }
            if (condition.getName() != null && !condition.getName().equals("")) {
                sql += " and e.name like '%" + condition.getName() + "%'";
            }
            if (condition.getSex() != null && !condition.getSex().equals("")) {
                sql += " and sex = '" + condition.getSex() + "'";
            }
            if (condition.getSalary1() != null && !condition.getSalary1().equals("")) {
                sql += " and salary >=" + condition.getSalary1();
            }
            if (condition.getSalary2() != null && !condition.getSalary2().equals("")) {
                sql += " and salary <=" + condition.getSalary2();
            }
        }
        sql += " order by e.id";
        sql += " limit ?,?";
        System.out.println(sql);
        int startIndex = (page - 1) * countPerPage;
        return super.query(sql, Employee.class, startIndex, countPerPage);
    }

    //绑定主窗体JTable的值
    public Object[][] getTableValue(List<Employee> list) {
        Object[][] obj = new Object[list.size()][7];
        for (int i = 0; i < list.size(); i++) {
            Employee eb = list.get(i);
            obj[i][0] = eb.getId();
            obj[i][1] = eb.getName();
            obj[i][2] = eb.getSex();
            obj[i][3] = eb.getDeptName();
            obj[i][4] = eb.getSalary();
            obj[i][5] = eb.getEmail();
            obj[i][6] = eb.getpNumber();
        }
        return obj;
    }

    //获取总页数
    public int getTotal(ConditionBean condition) {
        String sql = "select count(*) from empInfo e ,departinfo d where e.did=d.id ";
        if (condition != null) {
            if (condition.getDid() != 0) {
                sql += " and did='" + condition.getDid() + "'";
            }
            if (condition.getName() != null && !condition.getName().equals("")) {
                sql += " and e.name like '%" + condition.getName() + "%'";
            }
            if (condition.getSex() != null && !condition.getSex().equals("")) {
                sql += " and sex = '" + condition.getSex() + "'";
            }
            if (condition.getSalary1() != null && !condition.getSalary1().equals("")) {
                sql += " and salary >=" + condition.getSalary1();
            }
            if (condition.getSalary2() != null && !condition.getSalary2().equals("")) {
                sql += " and salary <=" + condition.getSalary2();
            }
        }
        int total = super.queryCount(sql).intValue();
        int pageCount = total / countPerPage;
        if (total % countPerPage != 0) {
            pageCount++;
        }
        return pageCount;
    }

    //添加新员工
    public int addEmp(Employee emp) {
        String sql = "insert into empinfo (name,sex,did,salary,email,pNumber)values(?,?,?,?,?,?)";
        Object[] params = {emp.getName(), emp.getSex(), emp.getDid(), emp.getSalary(), emp.getEmail(), emp.getpNumber()};
        return super.update(sql, params);
    }

    //删除方法（多行）
    public int delEmp(String[] ids) {
        String sql = "delete from empinfo where id in(";
        for (int i = 0; i < ids.length; i++) {
            if (i == ids.length - 1) {
                sql += ids[i];
            } else {
                sql += ids[i] + ",";
            }
        }
        sql += ")";
        return super.update(sql);
    }

    //修改员工信息
    public int updateEmp(Employee emp) {
        String sql = "update empInfo set name=?,sex=?,did=?,salary=?  where id=?";
        Object[] params = {emp.getName(), emp.getSex(), emp.getDid(), emp.getSalary(), emp.getId()};
        return super.update(sql, params);
    }


    //根据id查询员工
    public boolean getEmp(int id) {
        String sql = "select * from empinfo where id=?";
        Employee employee = super.queryOne(sql, Employee.class, id);
        return employee != null;
    }
}

